import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;

/**
 * @author 梁梓豪
 * Created by 梁梓豪 on 2019-04-02.
 */
@SuppressWarnings("Duplicates")
@WebServlet("/jdbc")
public class JdbcServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) {

        // 每次执行都新建一个连接
        try {

            Connection conn = null;
            Statement stmt = null;
            ResultSet rset = null;

            InitialContext context = new InitialContext();
            DataSource dataSource =
                    (DataSource) context.lookup("jdbc/DSTest");
            conn = dataSource.getConnection();
            stmt = conn.createStatement();

            String create = "    CREATE TABLE `Score` (\n" +
                    "      `studentid` int(11) unsigned NOT NULL AUTO_INCREMENT,\n" +
                    "      `coursename` varchar(100) NOT NULL,\n" +
                    "      `score` varchar(100) NOT NULL,\n" +
                    "      PRIMARY KEY (`studentid`)\n" +
                    "    );";

            if (stmt.executeUpdate(create) == 0) {

                String insert = "    insert into `Score` (`studentid`,`coursename`,`score`) values (1,'JavaEE',100);\n" +
                        "    insert into `Score` (`studentid`,`coursename`,`score`) values (2,'图像处理', 80);\n" +
                        "    insert into `Score` (`studentid`,`coursename`,`score`) values (3,'python数据分析', 90);";

                if(stmt.executeUpdate(insert) > 0) {
                    rset=stmt.executeQuery("select * from Score");
                    // 遍历每行记录
                    while (rset.next()) {//如果结果集中有数据，就会循环打印出来
                        resp.getWriter().println(rset.getInt("studentid")+","+rset.getString("coursename") + "," + rset.getInt("score"));
                    }
                }
            }
        } catch (Exception exc) {
        }
    }
}
